﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Emoney.DataAccess;
using LiuYan.Base;
using LiuYan.Entity;


namespace LiuYan.Service
{
   public class UserService
    {
       /// <summary>
       /// 编辑用户信息
       /// </summary>
       /// <param name="entity"></param>
       /// <returns></returns>
       public int Edit(TB_User entity)
       {
          SqlParameter[] parameters=new SqlParameter[]
          {
              new SqlParameter("@Id",SqlDbType.Int),
              new SqlParameter("@OpenId",SqlDbType.VarChar,200),
              new SqlParameter("@NcName",SqlDbType.VarChar,200),
              new SqlParameter("@Sex",SqlDbType.VarChar,200),
              new SqlParameter("@Img",SqlDbType.VarChar,200),
              new SqlParameter("@Tell",SqlDbType.VarChar,200),
              new SqlParameter("@Name",SqlDbType.VarChar,50)
          };
           parameters[0].Value = entity.Id;
           parameters[1].Value = entity.OpenId;
           parameters[2].Value = entity.NcName;
           parameters[3].Value = entity.Sex;
           parameters[4].Value = entity.Img;
           parameters[5].Value = entity.Tell;
           parameters[6].Value = entity.Name;
           string sql = entity.Id == 0
               ? "insert into [dbo].[TB_User](OpenId,NcName,Sex,Img,name,Tell,del)values(@OpenId,@NcName,@Sex,@Img,@Name,@Tell,0)"
               : "update [dbo].[TB_User] set NcName=@NcName,Sex=@Sex,Img=@Img,Tell=@Tell,Name=@Name where id=@Id";
           return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql,parameters);
       }

       /// <summary>
       /// 登陆
       /// </summary>
       /// <param name="openId"></param>
       /// <returns></returns>
       public TB_User Login(string openId)
       {
           TB_User entity=new TB_User();
           SqlParameter parameter=new SqlParameter("@openId",SqlDbType.VarChar,200);
           parameter.Value = openId;
           string sql = "select * from [dbo].[TB_User] where OpenId=@openId";
           var table= SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql, parameter).Tables[0];
           if (table.Rows.Count == 0)
           {
               return entity;
           }
           entity.Id = Convert.ToInt32(table.Rows[0]["Id"]);
           entity.Img = table.Rows[0]["Img"].ToString();
           entity.NcName = table.Rows[0]["NcName"].ToString();
           entity.Sex = Convert.ToInt32(table.Rows[0]["Sex"]);
           entity.Tell = table.Rows[0]["Tell"].ToString();
           entity.OpenId = table.Rows[0]["OpenId"].ToString();
           return entity;
       }

       /// <summary>
       /// 获取用户信息
       /// </summary>
       /// <param name="userid"></param>
       /// <returns></returns>
       public DataTable GetUserInfo(int userid)
       {
           string sql = "select * from TB_User where id="+userid;
           return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
       }

       /// <summary>
       /// 获取会员列表
       /// </summary>
       /// <param name="pageIndex"></param>
       /// <param name="pageCount"></param>
       /// <param name="name">会员姓名</param>
       /// <param name="tell">会员电话</param>
       /// <returns></returns>
       public DataSet GetList(int pageIndex, int pageCount,string name,string tell)
       {

           SqlParameter[] parameters = new SqlParameter[]
           {
               new SqlParameter("@name", SqlDbType.VarChar, 100),
                new SqlParameter("@tell", SqlDbType.VarChar, 100)
           };
           parameters[0].Value = name;
              parameters[1].Value = tell;
           string where = "";
           where = name == "" ? where : (where + " and Name like  N'%'+@name+'%' ");
           where = tell == "" ? where : (where + " and tell like  N'%'+@tell+'%' ");
           string sql = "select count(*) as count from [dbo].[TB_User]  where del=0 " +where+
                        "select top(" + pageCount + ")*,(select sum(money) from TB_UserMoney where TB_UserMoney.User_id=TB_User.id) as Money" +
                        " from dbo.TB_User where id not in(select top(" + pageIndex + ")" +
                        "Id from TB_User order by id desc) and del=0 "+where+"  order by id desc ";
           var set = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql,parameters);
           set.Tables[0].TableName = "count";
           set.Tables[1].TableName = "data";
           return set;
       }

       /// <summary>
       /// 修改金额
       /// </summary>
       /// <param name="money"></param>
       /// <param name="userid"></param>
       /// <returns></returns>
       public int EditMoney(float money,int userid)
       {
           string sql = "insert into dbo.TB_UserMoney(UserId,money)values('"+userid+"','"+money+"')";
           return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
       }
}
}